package com.ftwj.demo.utils.excel;

import cn.afterturn.easypoi.cache.manager.POICacheManager;
import cn.afterturn.easypoi.csv.CsvExportUtil;
import cn.afterturn.easypoi.csv.CsvImportUtil;
import cn.afterturn.easypoi.csv.entity.CsvExportParams;
import cn.afterturn.easypoi.csv.entity.CsvImportParams;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.ExcelXorHtmlUtil;
import cn.afterturn.easypoi.excel.entity.ExcelToHtmlParams;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.word.WordExportUtil;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.springframework.web.multipart.MultipartFile;

import com.ftwj.demo.utils.file.FileHelper;
import com.ftwj.demo.utils.file.FileUtils;
import com.ftwj.demo.utils.logs.LoggerUtil;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
* <p>Title: ExcelUtils</p>  
* <p>Description: Excel简单导入导出，Excel模板导出，Word模板导出，CVS导入导出，Excel转Html在线预览</p>
* <p>Copyright: Copyright (c) 2020 </p>  
* <p>Company: 兰州飞天网景信息产业有限公司</p>    
* @author HeYang
* @date 2020年4月24日 上午9:15:23
* @version V1.0
 */
public class ExcelUtils {
	
	/**
	 * <p>Title: exportExcel</p>  
	 * <p>Description: 导出   可以设置不创建表头</p>  
	 * @param response     
	 * @param fileName     文件名称
	 * @param title        标题
	 * @param sheetName    sheet名称
	 * @param list         pojo数据
	 * @param pojoClass    pojo类型
	 * @param type         导出类型
	 * @param isCreateHeader  是否创建表头
	 * @author HeYang
	 * @date 2020年4月27日  
	 */
    public static void exportExcel(HttpServletResponse response,String fileName,String title,String sheetName,List<?> list, Class<?> pojoClass,ExcelTypeEnum type,boolean isCreateHeader) throws IOException {
    	ExportParams exportParams = createExportParams(title,sheetName,type);//通用设置导出版本  xls或xlsx
        exportParams.setCreateHeadRows(isCreateHeader);//不设置默认为true
        defaultPjExport(response,fileName,list,pojoClass,exportParams);
    }

    /**
     * <p>Title: exportExcel</p>  
     * <p>Description: 设置表头，设置sheet 导出</p>  
     * @param response
     * @param fileName    文件名称
     * @param title       标题
     * @param sheetName   sheet名称
     * @param list        pojo数据
     * @param pojoClass   pojo类型
     * @param type        导出类型
     * @author HeYang
     * @date 2020年4月27日  
     */
    public static void exportExcel(HttpServletResponse response,String fileName,String title,String sheetName,List<?> list,Class<?> pojoClass,ExcelTypeEnum type) throws IOException {
    	ExportParams exportParams = createExportParams(title,sheetName,type);//通用设置导出版本  xls或xlsx
    	defaultPjExport(response,fileName,list,pojoClass,exportParams);
    }

    /**
     * <p>Title: exportExcel</p>  
     * <p>Description: 默认sheetName 导出</p>  
     * @param response
     * @param fileName     文件名称
     * @param title        标题
     * @param list         pojo数据
     * @param pojoClass    pojo类型
     * @param type         导出类型
     * @author HeYang
     * @date 2020年4月27日  
     */
    public static void exportExcel(HttpServletResponse response,String fileName,String title,List<?> list, Class<?> pojoClass,ExcelTypeEnum type) throws IOException {
    	ExportParams exportParams = createExportParams(title,"sheet",type);//通用设置导出版本  xls或xlsx
    	defaultPjExport(response,fileName,list,pojoClass,exportParams);
    }

    /**
     * excel 导出     直接传入list<Map>  待验证
     *
     * @param list     Map数据
     * @param fileName 文件名称
     * @param response
     */
    public static void exportExcel(HttpServletResponse response,String fileName,ExcelTypeEnum type,List<Map<String, Object>> list) throws IOException {
    	defaultListMapExport(response,fileName,type,list);
    }
    
    //----------------------------------------------------------------------------------------------------------------------------------

    /**
     * <p>Title: defaultPjExport</p>  
     * <p>Description: 根据pojo导出数据</p>  
     * @param response
     * @param fileName   文件名称
     * @param list       数据
     * @param pojoClass  pojo类型
     * @param exportParams  导出参数
     * @author HeYang
     * @date 2020年4月27日  
     */
    private static void defaultPjExport(HttpServletResponse response,String fileName,List<?> list,Class<?> pojoClass,ExportParams exportParams) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        downLoadExcel(response,fileName,workbook,exportParams.getType());
    }

    /**
     * <p>Title: defaultListMapExport</p>  
     * <p>Description: 根据ListMap导出数据</p>  
     * @param response
     * @param fileName       文件名称
     * @param type           导出类型
     * @param list           数据
     * @author HeYang
     * @date 2020年4月27日  
     */
    private static void defaultListMapExport(HttpServletResponse response,String fileName,ExcelTypeEnum type,List<Map<String, Object>> list) throws IOException {
    	ExcelType excelType = type.getType();
    	Workbook workbook = ExcelExportUtil.exportExcel(list,excelType);
        downLoadExcel(response,fileName,workbook,excelType);
    }
    
  //----------------------------------------------------------------------------------------------------------------------------------

    /**
     * <p>Title: downLoadExcel</p>  
     * <p>Description: 导出下载</p>  
     * @param response
     * @param fileName  文件名称
     * @param workbook  excel数据
     * @param excelType 导出类型
     * @author HeYang
     * @date 2020年4月27日  
     */
    private static void downLoadExcel(HttpServletResponse response,String fileName,  Workbook workbook,ExcelType excelType) throws IOException {
        try {
        	response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            if(excelType == ExcelType.HSSF) {
            	response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLS.getValue(), "UTF-8"));
            }else {
            	response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLSX.getValue(), "UTF-8"));
			}
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }
    
  //----------------------------------------------------------------------------------------------------------------------------------

    /**
     * <p>Title: importExcel</p>  
     * <p>Description: 根据pojo导入，可设置标题行，表头行</p>  
     * @param filePath    excel文件路径
     * @param titleRows   标题行
     * @param headerRows  表头行
     * @param pojoClass   pojo类型
     * @author HeYang
     * @date 2020年4月27日  
     */
    public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows,Class<T> pojoClass) throws Exception {
        if (StringUtils.isBlank(filePath))  return null;
        ImportParams params = createImportParams(titleRows, headerRows);//设置导入参数
        return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
    }

    /**
     * <p>Title: importExcel</p>  
     * <p>Description: 描述</p>  
     * @param file        文件
     * @param titleRows   标题行
     * @param headerRows  表头行
     * @param pojoClass   pojo类型
     * @author HeYang
     * @throws Exception 
     * @date 2020年4月27日  
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows,Integer headerRows,Class<T> pojoClass) throws Exception {
    	if (file == null) return null;
    	InputStream inputStream = file.getInputStream();
    	ImportParams params = createImportParams(titleRows, headerRows);//设置导入参数
        return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
    }
    
  //----------------------------------------------------------------------------------------------------------------------------------

    /**
     * <p>Title: templateListExport</p>  
     * <p>Description: 模板导出excel</p>  
     * @param response
     * @param request
     * @param list          导出数据
     * @param excelTempPath 模板路径
     * @param excelName     文件名称
     * @author HeYang
     * @date 2020年4月27日  
     */
    public static boolean templateListExport(HttpServletResponse response,HttpServletRequest request, List<?> list
			,String excelTempPath, String excelName) {
    	Map<String, Object> map = new HashMap<String, Object>();
        map.put("list", list);//参数填入
        return templateMapExport(response, request, map, excelTempPath, excelName);
    }
    
    /**
     * <p>Title: templateMapExport</p>  
     * <p>Description: 模板导出excel</p>  
     * @param response
     * @param map           导出数据
     * @param excelTempPath  模板路径
     * @param excelName   文件名称
     * @author HeYang
     * @date 2020年4月23日  
     */
	public static boolean templateMapExport(HttpServletResponse response,HttpServletRequest request, Map<String, Object> map
			,String excelTempPath, String excelName) {
        TemplateExportParams params = new TemplateExportParams(excelTempPath);// 获取导出excel指定模版
        String suffix = FileUtils.getFileSuffix(excelTempPath);
        params.setHeadingStartRow(0);// 标题开始行
        String[] sheetName = {"sheet1信息"};// 设置sheetName，若不设置该参数，则使用得原本得sheet名称
        params.setSheetName(sheetName);
        Workbook workbook = ExcelExportUtil.exportExcel(params, map);// 导出excel
        if(workbook == null) return false; // 判断数据
        try {
            String codedFileName = java.net.URLEncoder.encode(excelName, "UTF-8");
            OutputStream output = response.getOutputStream();
            BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);// 写出数据输出流到页面
            response.reset();
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String agent = request.getHeader("USER-AGENT").toLowerCase();
            if (agent.contains("firefox")) {
                response.setHeader("Content-Disposition", "attachment;filename=" +new String(excelName.getBytes(), "ISO8859-1")+"."+suffix);
	        } else {
	        	response.setHeader("Content-Disposition", "attachment;filename=" +codedFileName +"."+suffix);
	        }
            workbook.write(bufferedOutPut);
            bufferedOutPut.flush();
            bufferedOutPut.close();
            output.close();
        } catch (IOException e) {
            e.printStackTrace();
            return false;
        }
		return true;
	}
	
	//----------------------------------------------------------------------------------------------------------------------------------
	
    /**
     * 	导出word
     * <p>第一步生成替换后的word文件，只支持docx</p>
     * <p>第二步下载生成的文件</p>
     * <p>第三步删除生成的临时文件</p>
     * 	模版变量中变量格式：{{foo}}
     * @param templatePath word模板地址
     * @param temDir 生成临时文件存放地址
     * @param fileName 文件名
     * @param params 替换的参数
     * @param request HttpServletRequest
     * @param response HttpServletResponse
     */
    public static void exportWord(HttpServletRequest request, HttpServletResponse response,String templatePath, String temDir, String fileName, Map<String, Object> params) {
        if (!temDir.endsWith("/")){
            temDir = temDir + File.separator;
        }
        File dir = new File(temDir);
        if (!dir.exists()) {
            dir.mkdirs();
        }
        String tmpPath = "";
        tmpPath = temDir + fileName;
        try(
            XWPFDocument doc = WordExportUtil.exportWord07(templatePath, params);
            FileOutputStream fos = new FileOutputStream(tmpPath);	
        	) {
            doc.write(fos);
            response.setCharacterEncoding("UTF-8");
            response.addHeader("content-type", "application/x-download");
            response.addHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(fileName , "UTF-8"));
            OutputStream out = response.getOutputStream();
            doc.write(out);
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
            return;
        }
        FileHelper.deleteFile(tmpPath);
    }
    
  //----------------------------------------------------------------------------------------------------------------------------------

	/**
	 * <p>Title: exportCvs</p>  
	 * <p>Description: 导出cvs</p>  
	 * @param list      导出数据
	 * @param t         pojo类
	 * @param catalog   导出目录
	 * @param fileName  文件名称
	 * @author HeYang
	 * @date 2020年4月24日  
	 */
	public static <T> boolean exportCvs(List<T> list,Class<T> pojoClass,String catalog,String fileName) {
		CsvExportParams params = new CsvExportParams();
        params.setEncoding("GBK");
        File savefile = new File(catalog);
        if (!savefile.exists()) {
            savefile.mkdirs();
        }
        try {
			FileOutputStream fos = new FileOutputStream(catalog+fileName);
			CsvExportUtil.exportCsv(params, pojoClass, list, fos);
			fos.flush();
			fos.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			return false;
		} catch (IOException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
	//----------------------------------------------------------------------------------------------------------------------------------
	
	/**
	 * <p>Title: importCvs</p>  
	 * <p>Description: Cvs导入</p>  
	 * @param inputstream  文件输入流
	 * @param pojoClass    pojo
	 * @param params       Csv导入参数
	 * @author HeYang
	 * @date 2020年4月27日  
	 */
	public static <T> List<T> importCvs(InputStream inputstream,Class<T> pojoClass,CsvImportParams params){
		return CsvImportUtil.importCsv(inputstream,pojoClass,params);
	}
	
	//----------------------------------------------------------------------------------------------------------------------------------

	/**
	 * <p>Title: excelToHtml</p>  
	 * <p>Description: excel转html</p>  
	 * @param response
	 * @param path      文件路径
	 * @author HeYang
	 * @date 2020年4月27日  
	 */
	public static void excelToHtml(HttpServletResponse response, String path) {
		try {
			ExcelToHtmlParams params = new ExcelToHtmlParams(WorkbookFactory.create(POICacheManager.getFile(path)),true,"yes");
			response.getOutputStream().write(ExcelXorHtmlUtil.excelToHtml(params).getBytes());
		} catch (IOException e) {
			LoggerUtil.error("Excel预览失败！");
		}
	}
	
	//----------------------------------------------------------------------------------------------------------------------------------
	
	/**
	 * <p>Title: createExportParams</p>  
	 * <p>Description: 设置Excel导出参数</p>  
	 * @param title     标题
	 * @param sheetName sheet名称
	 * @param type      导出类型
	 * @author HeYang
	 * @date 2020年4月27日  
	 */
	private static ExportParams createExportParams(String title,String sheetName,ExcelTypeEnum type) {
		ExportParams exportParams = new ExportParams();
    	exportParams.setTitle(title);
    	exportParams.setSheetName(sheetName);
	    exportParams.setType(type.getType());
		return exportParams;
	}
	
	/**
	 * <p>Title: createImportParams</p>  
	 * <p>Description: 设置Excel导入参数</p>  
	 * @param titleRows  标题行
	 * @param headerRows 表头行
	 * @author HeYang
	 * @date 2020年4月27日  
	 */
	private static ImportParams createImportParams(Integer titleRows,Integer headerRows) {
		ImportParams importParams = new ImportParams();
    	importParams.setTitleRows(titleRows);
    	importParams.setHeadRows(headerRows);
		return importParams;
	}
	
}
